<?php

use \Core\Di;
use \think\Db;
use \Core\Exception;
use Core\Controller;

class IndexController extends  Controller
{
    private $filename = '数据库设计.xls';
    private $database_name = 'newmtytrade_0424';
    public function init()
    {

        parent::init();
        \Yaf\Loader::import('PHPExcel/PHPExcel.php');

    }


    public function indexAction()
    {
//        $this->redirect('/mobile/index/index');
        $data['title'] = 'execl导出';
        $this->getView()->assign($data);
    }


    // 导出
    public function exportAction()
    {
        $this->getRequest = Yaf\Dispatcher::getInstance()->getRequest();
        $hostname = $this->getRequest->getPost('hostname');
        $hostport = $this->getRequest->getPost('hostport');
        $username = $this->getRequest->getPost('username');
        $password = $this->getRequest->getPost('password');
        $databasename = $this->getRequest->getPost('databasename');

        if(empty($hostname) || empty($hostport) || empty($username) || empty($password) || empty($databasename)){
            exit('参数错误！请返回');
        }

        $this->database_name = $databasename;
        $data_Sheet1 = [];
        think\Db::setConfig([
            'type'            => 'mysql',
            'hostname'        =>  $hostname,
            'database'        => 'mysql',
            'username'        =>  $username,
            'password'        =>  $password,
            'hostport'        =>  $hostport,
            'prefix'          => '',
            'debug'           => true,
        ]);
        $data_Sheet1 = think\Db::name('db')->query("select (@i := @i + 1) as i ,table_name,table_comment
        from information_schema.tables s1,(SELECT(@i := 0)) s2 where table_schema='{$this->database_name}'  ");
//        var_dump($data_Sheet1);die;
        array_unshift($data_Sheet1, ['序号', '中文名', '物理名', '备注']);
        array_unshift($data_Sheet1, ['数据表目录']);
        array_unshift($data_Sheet1, []);

        $data_Sheet2 = [];
        $database_data = think\Db::name('db')->query("select (@i := @i + 1) as i ,table_name,table_comment
        from information_schema.tables s1,(SELECT(@i := 0)) s2 where table_schema='{$this->database_name}'  ");
        foreach ($database_data as $k => $v) {
            $data_Sheet2_one = think\Db::name('db')->query("select (@i := @i + 1) as i ,COLUMN_NAME,COLUMN_TYPE,COLUMN_DEFAULT,IS_NULLABLE,COLUMN_KEY,EXTRA,COLUMN_COMMENT
            from information_schema.COLUMNS s1,(SELECT(@i := 0)) s2
            where table_name = '{$v['table_name']}' and table_schema = '{$this->database_name}'");
            // echo $this->m_user->sql;die;
            array_unshift($data_Sheet2_one, ['序号', '字段名', '类型（长度）', '字段默认值', 'Null', 'Key', '其它', '备注']);
            $table_name = [];
            $table_name[] = '表(' . $v['i'] . ')' . $v['table_name'];
            array_unshift($data_Sheet2_one, $table_name);

            $data_Sheet2 = array_merge($data_Sheet2, $data_Sheet2_one);
        }

        $this->create_xls($data_Sheet1, $data_Sheet2, $this->filename);

        exit();

    }

    /**
     * 数组转xls格式的excel文件
     * @param  array $data 需要生成excel文件的数组
     * @param  string $filename 生成的excel文件名
     *      示例数据：
     * $data = array(
     * array(NULL, 2010, 2011, 2012),
     * array('Q1',   12,   15,   21),
     * array('Q2',   56,   73,   86),
     * array('Q3',   52,   61,   69),
     * array('Q4',   30,   32,    0),
     * );
     */
    private function create_xls($data_Sheet1, $data_Sheet2, $filename = 'simple.xls')
    {
        ini_set('max_execution_time', '0');
        $filename = str_replace('.xls', '', $filename) . '.xls';
        $phpexcel = new PHPExcel();
        $phpexcel->getProperties()
            ->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");
        $phpexcel->getActiveSheet()->fromArray($data_Sheet1);
        $phpexcel->getActiveSheet()->setTitle('数据表目录');

        $arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K');
        for ($i = 0, $j = count($arr); $i < $j; $i++) {

            $phpexcel->getActiveSheet()->getColumnDimension($arr[$i])->setWidth(20);
        }
        for ($i = 0, $j = 4; $i < $j; $i++) {
            $cell = $arr[$i] . '1';
            $cell_two = $arr[$i] . '3';
            $phpexcel->getActiveSheet()->getStyle($cell)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $phpexcel->getActiveSheet()->getStyle($cell)->getFill()->getStartColor()->setARGB('0017C405');
            $phpexcel->getActiveSheet()->getStyle($cell_two)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $phpexcel->getActiveSheet()->getStyle($cell_two)->getFill()->getStartColor()->setARGB('0017C405');

        }
        $phpexcel->getActiveSheet()->mergeCells('A2:D2');
        $phpexcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $styleThinBlackBorderOutline = array(
            'borders' => array(
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN //粗的是thick
                ),

            ),
        );
        $phpexcel->getActiveSheet()->getStyle('A1:D' . (count($data_Sheet1) + 1))->applyFromArray($styleThinBlackBorderOutline);

        //Create a new worksheet, after the default sheet 创建新的工作标签
        $phpexcel->createSheet();
        $phpexcel->setActiveSheetIndex(1);
        $phpexcel->getActiveSheet()->setTitle('数据表');
        $phpexcel->getActiveSheet()->fromArray($data_Sheet2);
        $arr = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K');
        for ($i = 0, $j = count($arr); $i < $j; $i++) {
            $phpexcel->getActiveSheet()->getColumnDimension($arr[$i])->setWidth(20);
        }
        foreach ($data_Sheet2 as $k => $v) {
            if (strpos($v[0], '表(') !== false) {
                $merge_cell = 'A' . ($k + 1) . ':H' . ($k + 1);
                $phpexcel->getActiveSheet()->mergeCells($merge_cell);
                for ($i = 0, $j = 8; $i < $j; $i++) {
                    $cell = $arr[$i] . ($k + 2);
                    $phpexcel->getActiveSheet()->getStyle($cell)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                    $phpexcel->getActiveSheet()->getStyle($cell)->getFill()->getStartColor()->setARGB('0017C405');

                }
                $phpexcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            }

        }
        $phpexcel->getActiveSheet()->getStyle('A1:H' . (count($data_Sheet2) + 1))->applyFromArray($styleThinBlackBorderOutline);

        $phpexcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=$filename");
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0
        $objwriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
        $objwriter->save('php://output');
        exit;
    }



}
